<template>
  <div class="container">
    <div class="searchbar" v-show="querybarVisible">
      <el-form :model="queryForm" ref="queryForm">
        <el-row>
          <el-col :span="8">
            <el-form-item label="数据库类型" prop="dbtype">
              <el-select
                v-model="queryForm.dbtype"
                placeholder="请选择数据库类型"
              >
                <el-option
                  v-for="item in dbtypeOptions"
                  :label="item.label"
                  :key="item.value"
                  :value="item.value"
                ></el-option>
              </el-select> </el-form-item></el-col
          ><el-col :span="8">
            <el-form-item label="应用系统名称" prop="appname">
              <el-autocomplete
                v-model="queryForm.appname"
                :fetch-suggestions="querySearchAsync"
                placeholder="请输入应用系统名称"
                @select="handleSelect"
              ></el-autocomplete> </el-form-item></el-col
          ><el-col :span="8">
            <el-form-item label="是否全表扫描" prop="fulltablescan">
              <el-select
                v-model="queryForm.fulltablescan"
                placeholder="请选择扫描方式"
              >
                <el-option
                  v-for="item in fullScanOptions"
                  :label="item.label"
                  :key="item.value"
                  :value="item.value"
                ></el-option>
              </el-select> </el-form-item
          ></el-col>
        </el-row>
        <el-row
          ><el-col :span="8">
            <el-form-item label="状态" prop="status">
              <el-select v-model="queryForm.status" placeholder="请选择状态">
                <el-option
                  v-for="item in stateOptions"
                  :label="item.label"
                  :key="item.value"
                  :value="item.value"
                ></el-option>
              </el-select> </el-form-item></el-col
          ><el-col :span="8">
            <el-form-item>
              <el-button
                type="primary"
                icon="el-icon-search"
                @click="searchData"
                >搜索</el-button
              >
              <el-button
                type="warning"
                icon="el-icon-refresh"
                @click="resetData"
                >重置</el-button
              ></el-form-item
            ></el-col
          ></el-row
        >
      </el-form>
    </div>
    <div class="main">
      <div class="toolbar">
        <el-button-group>
          <el-button
            type="primary"
            icon="el-icon-plus"
            @click="createSQLSummaryInfo"
            >添加</el-button
          >
          <!--el-button type="danger" icon="el-icon-delete">删除</el-button-->
          <el-button
            type="success"
            icon="el-icon-refresh"
            @click="refreshSQLSummaryInfo"
            >刷新</el-button
          >
        </el-button-group>
        <el-button
          type="primary"
          class="right-toolbar"
          plain
          icon="el-icon-search"
          @click="querybarVisible = !querybarVisible"
        ></el-button>
      </div>
      <el-table
        ref="table"
        :data="sqlData"
        style="width: 100%"
        border
        @row-dblclick="rowdblclick"
      >
      
        <el-table-column
          prop="stmttext"
          label="sql语句"
          show-overflow-tooltip
        ></el-table-column>
        <el-table-column prop="appname" label="系统名称"> </el-table-column>
        <el-table-column prop="env" label="环境类型" width="80">
          <template slot-scope="scope"
            ><span v-if="scope.row.env == 1">验证环境</span>
            <span v-else-if="scope.row.env == 2">压测环境</span>
            <span v-else-if="scope.row.env == 3">测试环境</span>
            <span v-else-if="scope.row.env == 4">开发环境</span></template
          ></el-table-column
        >
        <el-table-column prop="fulltablescan" label="全表扫描(前/后)">
          <template slot-scope="scope"
            ><span v-show="scope.row.beforefulltablescan == 0">否</span
            ><span v-show="scope.row.beforefulltablescan == 1">是</span>
            <span v-show="scope.row.beforefulltablescan == 2">未知</span>/
            <span v-show="scope.row.afterfulltablescan == 0">否</span
            ><span v-show="scope.row.afterfulltablescan == 1">是</span>
            <span v-show="scope.row.afterfulltablescan == 2"
              >未知</span
            ></template
          >
        </el-table-column>
        <el-table-column
          prop="numexecutions"
          label="次数"
          width="60"
        ></el-table-column>
        <el-table-column
          prop="stmtexectime"
          label="总耗时"
          width="60"
        ></el-table-column>
        <el-table-column
          prop="avgexectime"
          label="平均时间"
          width="80"
        ></el-table-column>
        <el-table-column prop="opplantime" label="预期解决时间" width="100">
          <template slot-scope="scope">
            <span>{{ transfromDate(scope.row.opplantime) }}</span></template
          ></el-table-column
        >
        <el-table-column prop="optime" label="解决日期" width="80"
          ><template slot-scope="scope">
            <span>{{ transfromDate(scope.row.optime) }}</span></template
          ></el-table-column
        >
        <el-table-column prop="optype" label="优化类型" width="80"
          ><template slot-scope="scope">
            <span
              v-show="item.value == scope.row.optype"
              v-for="item in optypeOptions"
              :key="item.value"
              >{{ item.label }}</span
            ></template
          ></el-table-column
        >
        <!--el-table-column prop="opcontent" label="优化内容"></el-table-column-->
        <el-table-column
          prop="tracker"
          label="跟踪人"
          width="80"
        ></el-table-column>
        <!--el-table-column
          prop="beforeavgexectime"
          label="修改前平均时间"
          width="120"
        ></el-table-column-->
        <el-table-column label="平均时间(前/后)" width="120">
          <template slot-scope="scope">
            <span
              >{{ scope.row.beforeavgexectime }}/{{
                scope.row.afteravgexectime
              }}</span
            ></template
          ></el-table-column
        >
         <el-table-column prop="sqlid" label="sql_id" v-if="dbtype == 2"></el-table-column>
        <el-table-column label="cost值(前/后)" width="120" v-if="dbtype == 2">
          <template slot-scope="scope">
            <span
              >{{ scope.row.beforecost }}/{{ scope.row.aftercost }}</span
            ></template
          ></el-table-column
        >
        <!--el-table-column
          prop="afteravgexectime"
          label="修改后平均时间"
          width="120"
        >
        </el-table-column-->
        <el-table-column prop="status" label="状态" width="80">
          <template slot-scope="scope"
            ><el-tag v-if="scope.row.status == 1" type="warning">待修改</el-tag>
            <el-tag v-else-if="scope.row.status == 2" type="danger">
              未处理</el-tag
            >
            <el-tag v-else-if="scope.row.status == 3" type="success"
              >无需修改</el-tag
            >
            <el-tag v-else-if="scope.row.status == 4" type="success"
              >已修改</el-tag
            ></template
          >
        </el-table-column>
        <el-table-column label="操作" width="90" align="center">
          <template slot-scope="scope">
            <el-button
              type="text"
              icon="el-icon-edit"
              @click="handleEdit(scope.$index, scope.row)"
              >编辑</el-button
            >
          </template>
        </el-table-column>
      </el-table>
      <div class="pagination">
        <el-pagination
          background
          layout="total, prev, pager, next"
          :current-page="pageIndex"
          :page-size="pageSize"
          :total="totalCount"
          @current-change="handlePageChange"
        ></el-pagination>
      </div>
      <!-- dialog -- 添加/编辑 -->
      <el-dialog
        :title="dialogOptions.title"
        :visible.sync="dialogOptions.visible"
        width="60%"
        :close-on-click-modal="false"
        @close="dialogOptions.visible = false"
      >
        <el-form :model="summaryForm" label-width="130px">
          <el-row>
            <el-col :span="24">
              <el-form-item label="sql语句" prop="stmttext">
                <el-input
                  v-model="summaryForm.stmttext"
                  type="textarea"
                  :disabled="dialogOptions.editable"
                ></el-input>
              </el-form-item>
            </el-col>
          </el-row>
          <el-row>
            <el-col :span="12">
              <el-form-item label="数据库类型" prop="dbtype">
                <el-select
                  v-model="summaryForm.dbtype"
                  style="width:100%"
                  :disabled="dialogOptions.editable"
                >
                  <el-option
                    v-for="item in dbtypeOptions"
                    :label="item.label"
                    :key="item.value"
                    :value="item.value"
                  ></el-option>
                </el-select> </el-form-item
            ></el-col>
            <el-col :span="12">
              <el-form-item label="应用系统名称" prop="appname">
                <!--el-input
                  v-model="summaryForm.appname"
                  :disabled="dialogOptions.editable"
                ></el-input-->
                <el-autocomplete
                  v-model="summaryForm.appname"
                  :fetch-suggestions="querySearchAsync"
                  placeholder="请输入应用系统名称"
                  @select="handleSelect"
                  :disabled="dialogOptions.editable"
                  style="width:100%"
                ></el-autocomplete></el-form-item
            ></el-col>
          </el-row>
          <el-row>
            <el-col :span="12">
              <el-form-item label="sql_id" prop="sqlid">
                <el-input
                  v-model="summaryForm.sqlid"
                  :disabled="summaryForm.dbtype != 2 || dialogOptions.editable"
                ></el-input> </el-form-item
            ></el-col>
            <el-col :span="12"
              ><el-form-item label="环境类型" prop="env">
                <el-select
                  v-model="summaryForm.env"
                  style="width:100%"
                  :disabled="dialogOptions.editable"
                >
                  <el-option
                    v-for="item in envOptions.slice(1)"
                    :label="item.label"
                    :key="item.value"
                    :value="item.value"
                  ></el-option>
                </el-select> </el-form-item
            ></el-col>
          </el-row>
          <el-row>
            <el-col :span="12">
              <el-form-item label="执行次数" prop="numexecutions">
                <el-input
                  v-model="summaryForm.numexecutions"
                  :disabled="dialogOptions.editable"
                ></el-input> </el-form-item
            ></el-col>
            <el-col :span="12">
              <el-form-item label="执行总耗时" prop="stmtexectime">
                <el-input
                  v-model="summaryForm.stmtexectime"
                  :disabled="dialogOptions.editable"
                ></el-input> </el-form-item
            ></el-col>
          </el-row>
          <el-row>
            <el-col :span="12">
              <el-form-item label="平均执行时间" prop="avgexectime">
                <el-input
                  v-model="summaryForm.avgexectime"
                  :disabled="dialogOptions.editable"
                ></el-input> </el-form-item
            ></el-col>
            <el-col :span="12">
              <el-form-item label="最大cost值" prop="maxcost">
                <el-input
                  v-model="summaryForm.maxcost"
                  :disabled="summaryForm.dbtype != 2 || dialogOptions.editable"
                ></el-input> </el-form-item
            ></el-col>
          </el-row>
          <el-row>
            <el-col :span="12">
              <el-form-item label="优化类型" prop="optype">
                <el-select
                  v-model="summaryForm.optype"
                  style="width:100%"
                  :disabled="dialogOptions.editable"
                >
                  <el-option
                    v-for="item in optypeOptions"
                    :label="item.label"
                    :key="item.value"
                    :value="item.value"
                  ></el-option>
                </el-select> </el-form-item
            ></el-col>
            <el-col :span="12">
              <el-form-item label="跟踪人" prop="tracker">
                <el-input
                  v-model="summaryForm.tracker"
                  :disabled="dialogOptions.editable"
                ></el-input> </el-form-item
            ></el-col>
          </el-row>
          <el-row>
            <el-col :span="12">
              <el-form-item label="预期解决时间" prop="opplantimes">
                <el-date-picker
                  v-model="summaryForm.opplantimes"
                  type="date"
                  placeholder="选择日期"
                  style="width:100%"
                  :disabled="dialogOptions.editable"
                  value-format="yyyy-MM-dd HH:mm:ss"
                >
                </el-date-picker> </el-form-item
            ></el-col>
            <el-col :span="12">
              <el-form-item label="解决日期" prop="optimes">
                <el-date-picker
                  v-model="summaryForm.optimes"
                  type="date"
                  placeholder="选择日期"
                  style="width:100%"
                  :disabled="dialogOptions.editable"
                  value-format="yyyy-MM-dd HH:mm:ss"
                >
                </el-date-picker> </el-form-item
            ></el-col>
          </el-row>
          <el-row>
            <el-col :span="12"
              ><el-form-item
                label="修改前是否全表扫描"
                prop="beforefulltablescan"
              >
                <el-select
                  v-model="summaryForm.beforefulltablescan"
                  style="width:100%"
                  :disabled="dialogOptions.editable"
                >
                  <el-option
                    v-for="item in fullScanOptions.slice(1)"
                    :label="item.label"
                    :key="item.value"
                    :value="item.value"
                  ></el-option>
                </el-select> </el-form-item
            ></el-col>
            <el-col :span="12">
              <el-form-item
                label="修改后是否全表扫描"
                prop="afterfulltablescan"
              >
                <el-select
                  v-model="summaryForm.afterfulltablescan"
                  style="width:100%"
                  :disabled="dialogOptions.editable"
                >
                  <el-option
                    v-for="item in fullScanOptions.slice(1)"
                    :label="item.label"
                    :key="item.value"
                    :value="item.value"
                  ></el-option>
                </el-select> </el-form-item
            ></el-col>
          </el-row>
          <el-row>
            <el-col :span="12">
              <el-form-item label="修改前平均执行时间" prop="beforeavgexectime">
                <el-input
                  v-model="summaryForm.beforeavgexectime"
                  :disabled="dialogOptions.editable"
                ></el-input> </el-form-item
            ></el-col>
            <el-col :span="12"
              ><el-form-item label="修改后平均执行时间" prop="afteravgexectime">
                <el-input
                  v-model="summaryForm.afteravgexectime"
                  :disabled="dialogOptions.editable"
                ></el-input> </el-form-item
            ></el-col>
          </el-row>
          <el-row>
            <el-col :span="12">
              <el-form-item label="修改前cost值" prop="beforecost">
                <el-input
                  v-model="summaryForm.beforecost"
                  :disabled="summaryForm.dbtype != 2 || dialogOptions.editable"
                ></el-input> </el-form-item
            ></el-col>
            <el-col :span="12">
              <el-form-item label="修改后cost值" prop="aftercost">
                <el-input
                  v-model="summaryForm.aftercost"
                  :disabled="summaryForm.dbtype != 2 || dialogOptions.editable"
                ></el-input> </el-form-item
            ></el-col>
          </el-row>
          <el-row>
            <el-col :span="12"
              ><el-form-item label="状态" prop="status">
                <el-select
                  v-model="summaryForm.status"
                  style="width:100%"
                  :disabled="dialogOptions.editable"
                >
                  <el-option
                    v-for="item in stateOptions.slice(1)"
                    :label="item.label"
                    :key="item.value"
                    :value="item.value"
                  ></el-option>
                </el-select> </el-form-item
            ></el-col>
          </el-row>
          <el-row>
            <el-col :span="24"
              ><el-form-item label="优化内容" prop="opcontent">
                <el-input
                  v-model="summaryForm.opcontent"
                  type="textarea"
                  :disabled="dialogOptions.editable"
                ></el-input> </el-form-item
            ></el-col>
          </el-row>
          <el-row>
            <el-col :span="24">
              <el-form-item label="用途描述" prop="purpose">
                <el-input
                  v-model="summaryForm.purpose"
                  type="textarea"
                  :disabled="dialogOptions.editable"
                ></el-input> </el-form-item
            ></el-col>
          </el-row>
        </el-form>
        <span slot="footer" class="dialog-footer">
          <el-button @click="dialogOptions.visible = false">取 消</el-button>
          <el-button
            type="primary"
            @click="handleDialogClick(dialogOptions.sqlmd5)"
            >确 定</el-button
          >
        </span>
      </el-dialog>
    </div>
  </div>
</template>

<script>
import service from "../../api/service.js";
export default {
  data() {
    return {
      isOracle: false,
      dialogVisible: false,
      // 搜索栏是否显示
      querybarVisible: true,
      // 搜索表单
      queryForm: {
        appid: null,
        fulltablescan: null,
        status: null,
        dbtype: 0
      },
      pageIndex: 1,
      pageSize: 10,
      totalCount: 0,
      sqlData: [],
      stateOptions: [
        { label: "全部", value: 0 },
        { label: "待修改", value: 1 },
        { label: "未处理", value: 2 },
        { label: "无需修改", value: 3 },
        { label: "已修改", value: 4 }
      ],
      fullScanOptions: [
        { label: "全部", value: -1 },
        { label: "否", value: 0 },
        { label: "是", value: 1 },
        { label: "未知", value: 2 }
      ],
      envOptions: [
        { label: "全部", value: -1 },
        { label: "验证环境", value: 1 },
        { label: "压测环境", value: 2 },
        { label: "测试环境", value: 3 },
        { label: "开发环境", value: 4 }
      ],
      appOptions: [],
      dbtypeOptions: [
        { label: "MYSQL", value: 0 },
        { label: "DB2", value: 1 },
        { label: "ORACLE", value: 2 }
      ],
      optypeOptions: [
        { label: "无", value: 0 },
        { label: "索引优化", value: 1 },
        { label: "sql优化", value: 2 },
        { label: "表设计调整", value: 3 },
        { label: "业务逻辑优化", value: 4 },
        { label: "参数配置优化", value: 5 },
        { label: "增加机器配置", value: 6 },
        { label: "升级版本", value: 7 }
      ],
      summaryForm: {
        sqlmd5: "",
        sqlid: "",
        stmttext: "", //sql语句
        appname: "", //应用系统名称
        env: null, //环境类型
        beforefulltablescan: null, //是否全表扫描
        afterfulltablescan: null,
        numexecutions: null, //执行次数
        stmtexectime: null, //执行总耗时
        avgexectime: null, //平均执行时间
        opplantimes: new Date(), //预期解决时间
        optimes: new Date(), //解决日期
        optype: "", //优化类型
        opcontent: "", //优化内容
        tracker: "", //跟踪人
        beforeavgexectime: null, //修改前平均时间
        afteravgexectime: null, //修改后平均时间
        maxcost: null,
        beforecost: null,
        aftercost: null,
        status: null,
        dbtype: null
      },
      dialogOptions: {
        title: "新增信息",
        sqlmd5: "",
        visible: false,
        editable: false
      },
      dbtype: 0
    };
  },
  mounted() {
    service.getApps().then(resp => {
      if (resp) {
        resp.forEach(s => (s.value = s.appname));
        this.appOptions = resp;
      }
    });
    this.querySqls();
  },
  methods: {
    rowdblclick(row, column, event) {
      this.dialogOptions = {
        title: "查看信息",
        sqlmd5: row.sqlmd5,
        visible: true,
        editable: true
      };
      for (var key in this.summaryForm) {
        this.summaryForm[key] = row[key];
      }
      this.summaryForm.dbtype = this.dbtype;
    },
    transfromDate(dt) {
      let date = new Date(dt);
      return (
        date.getFullYear() + "-" + (date.getMonth() + 1) + "-" + date.getDate()
      );
    },
    createSQLSummaryInfo() {
      this.resetSummaryForm();
      this.dialogOptions = {
        title: "新增信息",
        sqlmd5: "",
        visible: true,
        editable: false
      };
    },
    refreshSQLSummaryInfo() {
      this.querySqls();
    },
    resetSummaryForm() {
      this.summaryForm = {
        sqlmd5: "",
        sqlid: "",
        stmttext: "", //sql语句
        appname: "", //应用系统名称
        env: null, //环境类型
        beforefulltablescan: null, //是否全表扫描
        afterfulltablescan: null,
        numexecutions: null, //执行次数
        stmtexectime: null, //执行总耗时
        avgexectime: null, //平均执行时间
        opplantimes: new Date(), //预期解决时间
        optimes: new Date(), //解决日期
        optype: "", //优化类型
        opcontent: "", //优化内容
        tracker: "", //跟踪人
        beforeavgexectime: null, //修改前平均时间
        afteravgexectime: null, //修改后平均时间
        maxcost: null,
        beforecost: null,
        aftercost: null,
        status: null,
        dbtype: null
      };
    },
    handleEdit(idx, row) {
      this.resetSummaryForm();
      this.dialogOptions = {
        title: "编辑信息",
        sqlmd5: row.sqlmd5,
        visible: true,
        editable: false
      };
      for (var key in this.summaryForm) {
        this.summaryForm[key] = row[key];
        if (key == "optimes") {
          this.summaryForm["optimes"] = row["optime"];
        }
        if (key == "opplantimes") {
          this.summaryForm["opplantimes"] = row["opplantime"];
        }
      }
      this.summaryForm.dbtype = this.dbtype;
    },
    handleDialogClick(md5) {
      let _this = this;
      if (!md5) {
        service.createSQLSummaryInfo(this.summaryForm).then(resp => {
          if (resp) {
            _this.$message({
              message: "保存sql信息成功",
              type: "success"
            });
            _this.sqlData.push(resp);
            _this.resetSummaryForm();
          }
        });
      } else {
        service.updateSQLSummaryInfo(_this.summaryForm).then(resp => {
          if (resp) {
            _this.$message({
              message: "保存sql信息成功",
              type: "success"
            });
            let row = _this.sqlData.find(c => c.sqlmd5 == md5);
            for (var key in row) {
              row[key] = _this.summaryForm[key];
              if (key == "optime") {
                row["optime"] = _this.summaryForm["optimes"];
              }
              if (key == "opplantime") {
                row["opplantime"] = _this.summaryForm["opplantimes"];
              }
            }
            _this.resetSummaryForm();
          } else {
            _this.$message({
              message: "保存sql信息异常",
              type: "warning"
            });
          }
        });
      }
      this.dialogOptions.visible = false;
    },
    /*
     * 分页查询
     */
    handlePageChange(val) {
      this.pageIndex = val;
      this.querySqls();
    },
    /*
     * 分页查询组件信息
     */
    querySqls() {
      let _this = this;
      this.loading = true;
      service
        .querySQLSummaryInfos(
          this.pageIndex,
          this.pageSize,
          this.queryForm.dbtype,
          this.queryForm.appid ? this.queryForm.appid : 0,
          this.queryForm.fulltablescan ? this.queryForm.fulltablescan : -1,
          this.queryForm.status ? this.queryForm.status : -1
        )
        .then(resp => {
          if (resp) {
            _this.sqlData = resp.content;
            _this.totalCount = resp.totalElements;
            _this.dbtype = this.queryForm.dbtype;
            this.$refs.table.doLayout();
          }
          _this.loading = false;
        });
    },
    querySearchAsync(queryString, cb) {
      var appOptions = this.appOptions;
      var results = queryString
        ? appOptions.filter(this.createStateFilter(queryString))
        : appOptions;
      cb(results);
    },
    createStateFilter(queryString) {
      return state => {
        return (
          state.value.toLowerCase().indexOf(queryString.toLowerCase()) !== -1
        );
      };
    },
    handleSelect(item) {
      this.queryForm.appid = item.appid;
    },
    searchData() {
      this.querySqls();
    },
    resetData() {
      this.queryForm = {
        appid: null,
        fulltablescan: null,
        status: null,
        dbtype: 0
      };
    }
  }
};
</script>

<style>
.demo-table-expand {
  font-size: 0;
}
.demo-table-expand label {
  width: 160px;
  color: #99a9bf;
}
.demo-table-expand .el-form-item {
  margin-right: 0;
  margin-bottom: 0;
  width: 50%;
}
</style>